﻿USE [master]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DBDentalClinic')
DROP DATABASE [DBDentalClinic]
GO

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'DBDentalClinic')
BEGIN
CREATE DATABASE [DBDentalClinic]
END
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [DBDentalClinic] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [DBDentalClinic] SET ANSI_NULLS OFF
GO
ALTER DATABASE [DBDentalClinic] SET ANSI_PADDING OFF
GO
ALTER DATABASE [DBDentalClinic] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [DBDentalClinic] SET ARITHABORT OFF
GO
ALTER DATABASE [DBDentalClinic] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [DBDentalClinic] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [DBDentalClinic] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [DBDentalClinic] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [DBDentalClinic] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [DBDentalClinic] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [DBDentalClinic] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [DBDentalClinic] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [DBDentalClinic] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [DBDentalClinic] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [DBDentalClinic] SET  DISABLE_BROKER
GO
ALTER DATABASE [DBDentalClinic] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [DBDentalClinic] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [DBDentalClinic] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [DBDentalClinic] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [DBDentalClinic] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [DBDentalClinic] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [DBDentalClinic] SET  READ_WRITE
GO
ALTER DATABASE [DBDentalClinic] SET RECOVERY SIMPLE
GO
ALTER DATABASE [DBDentalClinic] SET  MULTI_USER
GO
ALTER DATABASE [DBDentalClinic] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [DBDentalClinic]SET DB_CHAINING OFF
GO
USE [DBDentalClinic]
GO

/****** Object:  Table [dbo].[Manager]    Script Date: 03/04/2012 16:51:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Manager](
	[CiManager] [int] NOT NULL,
	[FirstName] [nvarchar](30) NOT NULL,
	[LastName] [nvarchar](30) NOT NULL,
	[Address] [nvarchar](30) NOT NULL,
	[CellPhone] [int] NOT NULL,
	[Phone] [int] NULL,
	[DateInscription] [datetime] NOT NULL,
	[Email] [nvarchar](30) NOT NULL,
	[Gender] [nvarchar](15) NOT NULL,
	[UserName] [nvarchar](30) NOT NULL,
	[Password] [nvarchar](30) NOT NULL,
	[StateManager] [bit]NOT NULL,
	[ImageManager] [nvarchar](200) NULL,
 CONSTRAINT [PK_Manager] PRIMARY KEY CLUSTERED 
(
	[CiManager] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Patient](
	[CiPatient] [int] NOT NULL,
	[FirstName] [nvarchar](30) NOT NULL,
	[LastName] [nvarchar](30) NOT NULL,
	[Gender] [nvarchar](15) NOT NULL,
	[BirthDay] [datetime] NOT NULL,
	[CellPhone] [int] NOT NULL,
	[Phone] [int] NULL,	
	[Address] [nvarchar](30) NULL,
	[Email] [nvarchar](30) NULL,
	[StatePatient] [bit]NOT NULL,
	[ImagePatient] [nvarchar](200) NULL,	
 CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED 
(
	[CiPatient] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Diagnostic](
	[IdDiagnostic] [int] IDENTITY(1,1) NOT NULL,
	[DateDiagnostic] [dateTime] NOT NULL,	
	[NameDiagnostic] [nvarchar](1500) NULL,
	[NameTreatment] [nvarchar](1500) NULL,
	[AdverseCircumstances] [nvarchar](1500) NULL,
	[Observation] [nvarchar](500) NULL,
	[StateDiagnostic] [nvarchar](15)NOT NULL,
	[Patient_CiPatient] [int] NOT NULL,
	[Manager_CiDoctor] [int] NOT NULL,
 CONSTRAINT [PK_Diagnostic] PRIMARY KEY CLUSTERED 
(
	[IdDiagnostic] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[NoteBook](
	[IdNoteBook] [int] IDENTITY(1,1) NOT NULL,
	[NameNoteBook] [nvarchar](30)NOT NULL,
	[StateNoteBook] [nvarchar](15) NOT NULL,
 CONSTRAINT [PK_NoteBook] PRIMARY KEY CLUSTERED 
(
	[IdNoteBook] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Appointment](
	[DateAppointment] [nvarchar](30) NOT NULL,
	[TimeAppointment] [nvarchar](30) NOT NULL,
	[StateAppointment] [nvarchar](15) NOT NULL,
	[NoteBook_IdNoteBook] [int] NOT NULL,
	[Patient_CiPatient] [int] NOT NULL,
 CONSTRAINT [PK_Appointment] PRIMARY KEY CLUSTERED 
(
	[DateAppointment] ASC,
	[TimeAppointment] ASC,
	[NoteBook_IdNoteBook] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ManagerNoteBook] (
    [Manager_CiManager] [int]  NOT NULL,
    [NoteBook_IdNoteBook] [int]  NOT NULL,
 CONSTRAINT [PK_ManagerNoteBook] PRIMARY KEY CLUSTERED 
(
	[Manager_CiManager]	 ASC,
	[NoteBook_IdNoteBook] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Treatment](
	[IdTreatment] [int] NOT NULL,		
	[StateTreatment] [nvarchar](50) NOT NULL,
	[Diagnostic_IdDiagnostic] [int] NOT NULL,
 CONSTRAINT [PK_Treatment] PRIMARY KEY CLUSTERED 
(
	[IdTreatment] ASC
	--[Diagnostic_IdDiagnostic] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ImageTreatment](
	[IdImageTreatment] [int] IDENTITY(1,1) NOT NULL,		
	[ImageXRay] [nvarchar](200) NOT NULL,
	[Description] [nvarchar](200) NOT NULL,
	[Treatment_IdTreatment][int]NOT NULL,
 CONSTRAINT [PK_ImageTreatment] PRIMARY KEY CLUSTERED 
(	
	[IdImageTreatment] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[GeneralTreatment](
	[IdGeneralTreatment] [int] IDENTITY(1,1) NOT NULL,	
	[NameTreatment] [nvarchar](150) NOT NULL,
	[Description] [nvarchar](150) NULL,
	[Treatment_IdTreatment] [int] NOT NULL,
 CONSTRAINT [PK_GeneralTreatment] PRIMARY KEY CLUSTERED 
(	
	[IdGeneralTreatment] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ToothTreatment](
	[IdToothTreatment] [int] IDENTITY(1,1) NOT NULL,	
	[NamberTooth] [int] NOT NULL,
	[TypeTooth] [nvarchar](30) NOT NULL,
	[LocationTooth] [nvarchar](30) NOT NULL,
	[NameTreatment] [nvarchar](50) NULL,
	[Description] [nvarchar](200) NULL,
	[ImageTooth] [nvarchar](200) NULL,
	[Treatment_IdTreatment] [int] NOT NULL,
 CONSTRAINT [PK_ToothTreatment] PRIMARY KEY CLUSTERED 
(	
	[IdToothTreatment] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Role](
	[IdRole] [int] IDENTITY(1,1) NOT NULL,
	[NameRole] [nvarchar](30) NOT NULL,
	[Description] [nvarchar](200) NULL,
 CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED 
(
	[IdRole] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ManagerRole](
	[Manager_CiManager] [int] NOT NULL,
	[Role_IdRole] [int] NOT NULL,
 CONSTRAINT [PK_ManagerRole] PRIMARY KEY CLUSTERED 
(
	[Manager_CiManager] ASC,
	[Role_IdRole] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Function](
	[IdFunction] [int] IDENTITY(1,1) NOT NULL,
	[NameFunction] [nvarchar](30) NOT NULL,
	[Description] [nvarchar](200) NULL,
 CONSTRAINT [PK_Function] PRIMARY KEY CLUSTERED 
(
	[IdFunction] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[RoleFunction](
	[Role_IdRole] [int] NOT NULL,
	[Function_IdFunction] [int] NOT NULL,
 CONSTRAINT [PK_RoleFunction] PRIMARY KEY CLUSTERED 
(
	[Role_IdRole] ASC,
	[Function_IdFunction] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Product](
	[IdProduct] [int] NOT NULL,
	[GroupProduct] [nvarchar](30) NOT NULL,
	[NameProduct] [nvarchar](30) NOT NULL,
	[StockCurrent] [int] NOT NULL,	
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[IdProduct] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Buy](
	[IdBuyProduct] [int] IDENTITY(100,1) NOT NULL,	
	[DateBuy] [datetime] NOT NULL,
	[Supplier] [nvarchar](30) NOT NULL,
	[StockOrder] [int] NOT NULL,
	[UnitPrice] [float] NOT NULL,
	[TotalPrice] [float] NOT NULL,
	[ImageBuy] [nvarchar](200) NULL,
	[Description] [nvarchar](200) NULL,
	[Product_IdProduct] [int] NOT NULL,
	[Manager_CiManager] [int] NOT NULL,
 CONSTRAINT [PK_Buy] PRIMARY KEY CLUSTERED 
(
	[IdBuyProduct] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Payment](
	[NAccount] [int] IDENTITY(1,1) NOT NULL,
	[CiPatient] [int] NOT NULL,
	[CiUser] [int] NOT NULL,
	[DatePayment] [nvarchar](10) NOT NULL,
	[Amount] [float] NOT NULL,
	[Balance] [float] NOT NULL,
	[TotalPayment] [float] NOT NULL,
	[Description] [nvarchar](200) NULL,
 CONSTRAINT [PK_Payment] PRIMARY KEY CLUSTERED 
(
	[NAccount] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


/**********Creating foreign key **********/

/****** Object:  ForeignKey [FK_RoleUser_Manager]    Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[ManagerRole]  WITH CHECK ADD  CONSTRAINT [FK_ManagerRole_Manager] FOREIGN KEY([Manager_CiManager])
REFERENCES [dbo].[Manager] ([CiManager])
GO
ALTER TABLE [dbo].[ManagerRole] CHECK CONSTRAINT [FK_ManagerRole_Manager]
GO
/****** Object:  ForeignKey [FK_RoleUser_Roles]    Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[ManagerRole]  WITH CHECK ADD  CONSTRAINT [FK_ManagerRole_Role] FOREIGN KEY([Role_IdRole])
REFERENCES [dbo].[Role] ([IdRole])
GO
ALTER TABLE [dbo].[ManagerRole] CHECK CONSTRAINT [FK_ManagerRole_Role]
GO
/****** Object:  ForeignKey [FK_RoleFunction_Function]    Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[RoleFunction]  WITH CHECK ADD  CONSTRAINT [FK_RoleFunction_Function] FOREIGN KEY([Function_IdFunction])
REFERENCES [dbo].[Function] ([IdFunction])
GO
ALTER TABLE [dbo].[RoleFunction] CHECK CONSTRAINT [FK_RoleFunction_Function]
GO
/****** Object:  ForeignKey [FK_FunctionRole_Roles]    Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[RoleFunction]  WITH CHECK ADD  CONSTRAINT [FK_RoleFunction_Role] FOREIGN KEY([Role_IdRole])
REFERENCES [dbo].[Role] ([IdRole])
GO
ALTER TABLE [dbo].[RoleFunction] CHECK CONSTRAINT [FK_RoleFunction_Role]
GO
/****** Object:  ForeignKey [FK_Diagnostic_Manager]    Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[Diagnostic]  WITH CHECK ADD  CONSTRAINT [FK_Diagnostic_Manager] FOREIGN KEY([Manager_CiDoctor])
REFERENCES [dbo].[Manager] ([CiManager])
GO
ALTER TABLE [dbo].[Diagnostic] CHECK CONSTRAINT [FK_Diagnostic_Manager]
GO
/****** Object:  ForeignKey [FK_Diagnostic_Patient]    Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[Diagnostic]  WITH CHECK ADD  CONSTRAINT [FK_Diagnostic_Patient] FOREIGN KEY([Patient_CiPatient])
REFERENCES [dbo].[Patient] ([CiPatient])
GO
ALTER TABLE [dbo].[Diagnostic] CHECK CONSTRAINT [FK_Diagnostic_Patient]
GO
/****** Object:  ForeignKey [FK_Buy_Manager]    Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[Buy]  WITH CHECK ADD  CONSTRAINT [FK_Buy_Manager] FOREIGN KEY([Manager_CiManager])
REFERENCES [dbo].[Manager] ([CiManager])
GO
ALTER TABLE [dbo].[Buy] CHECK CONSTRAINT [FK_Buy_Manager]
GO
/****** Object:  ForeignKey [FK_Buy_Product]    Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[Buy]  WITH CHECK ADD  CONSTRAINT [FK_Buy_Product] FOREIGN KEY([Product_IdProduct])
REFERENCES [dbo].[Product] ([IdProduct])
GO
ALTER TABLE [dbo].[Buy] CHECK CONSTRAINT [FK_Buy_Product]
GO

/****** Object:  ForeignKey [FK_Treatment_Diagnostic]    Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[Treatment]  WITH CHECK ADD  CONSTRAINT [FK_Treatment_Diagnostic] FOREIGN KEY([Diagnostic_IdDiagnostic])
REFERENCES [dbo].[Diagnostic] ([IdDiagnostic])
GO
ALTER TABLE [dbo].[Treatment] CHECK CONSTRAINT [FK_Treatment_Diagnostic]
GO
/****** Object:  ForeignKey [FK_ImageTreatment_Treatment]    Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[ImageTreatment]  WITH CHECK ADD  CONSTRAINT [FK_ImageTreatment_Treatment] FOREIGN KEY([Treatment_IdTreatment])
REFERENCES [dbo].[Treatment] ([IdTreatment])
GO
ALTER TABLE [dbo].[ImageTreatment] CHECK CONSTRAINT [FK_ImageTreatment_Treatment]
GO
/****** Object:  ForeignKey [FK_ManagerNoteBook_Manager]    Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[ManagerNoteBook]  WITH CHECK ADD  CONSTRAINT [FK_ManagerNoteBook_Manager] FOREIGN KEY([Manager_CiManager])
REFERENCES [dbo].[Manager] ([CiManager])
GO
ALTER TABLE [dbo].[ManagerNoteBook] CHECK CONSTRAINT [FK_ManagerNoteBook_Manager]
GO
/****** Object:  ForeignKey [FK_ManagerNoteBook_NoteBook]   Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[ManagerNoteBook]  WITH CHECK ADD  CONSTRAINT [FK_ManagerNoteBook_NoteBook] FOREIGN KEY([NoteBook_IdNoteBook])
REFERENCES [dbo].[NoteBook] ([IdNoteBook])
GO
ALTER TABLE [dbo].[ManagerNoteBook] CHECK CONSTRAINT [FK_ManagerNoteBook_NoteBook]
GO
/****** Object:  ForeignKey [FK_Appointment_NoteBook]  Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[Appointment]  WITH CHECK ADD  CONSTRAINT [FK_Appointment_NoteBook] FOREIGN KEY([NoteBook_IdNoteBook])
REFERENCES [dbo].[NoteBook] ([IdNoteBook])
GO
ALTER TABLE [dbo].[Appointment] CHECK CONSTRAINT [FK_Appointment_NoteBook]
GO
/****** Object:  ForeignKey [FK_Appointment_NoteBook]  Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[Appointment]  WITH CHECK ADD  CONSTRAINT [FK_Appointment_Patient] FOREIGN KEY([Patient_CiPatient])
REFERENCES [dbo].[Patient] ([CiPatient])
GO
ALTER TABLE [dbo].[Appointment] CHECK CONSTRAINT [FK_Appointment_Patient]
GO

/****** Object:  ForeignKey [FK_GeneralTreatment_Treatment]    Script Date: 04/18/2012 19:42:27 ******/
ALTER TABLE [dbo].[GeneralTreatment]  WITH CHECK ADD  CONSTRAINT [FK_GeneralTreatment_Treatment] FOREIGN KEY([Treatment_IdTreatment])
REFERENCES [dbo].[Treatment] ([IdTreatment])
GO
ALTER TABLE [dbo].[GeneralTreatment] CHECK CONSTRAINT [FK_GeneralTreatment_Treatment]
GO
/****** Object:  ForeignKey [FK_ToothTreatment_Treatment]    Script Date: 04/18/2012 19:42:27 ******/
ALTER TABLE [dbo].[ToothTreatment]  WITH CHECK ADD  CONSTRAINT [FK_ToothTreatment_Treatment] FOREIGN KEY([Treatment_IdTreatment])
REFERENCES [dbo].[Treatment] ([IdTreatment])
GO
ALTER TABLE [dbo].[ToothTreatment] CHECK CONSTRAINT [FK_ToothTreatment_Treatment]
GO

/****** Object:  ForeignKey [FK_Payment_Manager]    Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[Payment]  WITH CHECK ADD  CONSTRAINT [FK_Payment_Manager] FOREIGN KEY([CiUser])
REFERENCES [dbo].[Manager] ([CiManager])
GO
ALTER TABLE [dbo].[Payment] CHECK CONSTRAINT [FK_Payment_Manager]
GO
/****** Object:  ForeignKey [FK_Payment_Patient]    Script Date: 05/09/2012 21:50:44 ******/
ALTER TABLE [dbo].[Payment]  WITH CHECK ADD  CONSTRAINT [FK_Payment_Patient] FOREIGN KEY([CiPatient])
REFERENCES [dbo].[Patient] ([CiPatient])
GO
ALTER TABLE [dbo].[Payment] CHECK CONSTRAINT [FK_Payment_Patient]
GO
----insert into DataBases--

INSERT [dbo].[Role] ([NameRole], [Description]) VALUES (N'Manager', NULL)
INSERT [dbo].[Role] ([NameRole], [Description]) VALUES (N'Doctor', NULL)
INSERT [dbo].[Role] ([NameRole], [Description]) VALUES (N'Secretary', NULL)

INSERT [dbo].[Manager] ([CiManager], [FirstName], [LastName], [Address], [CellPhone], [Phone], [DateInscription], [Email], [Gender], [UserName], [Password], [StateManager], [ImageManager]) VALUES (N'4494524', N'Leonardo', N'Mejia', N'Avenida Circunvalación', 72798653, 4495430, N'04/03/2012', N'leo@hotmail.com.es', N'Male', N'leo', N'leo123leo', 1, N'leo.jpg')
INSERT [dbo].[Manager] ([CiManager], [FirstName], [LastName], [Address], [CellPhone], [Phone], [DateInscription], [Email], [Gender], [UserName], [Password], [StateManager], [ImageManager]) VALUES (N'6367889', N'Alex', N'Flores', N'Mariscal Santa Cruz', 77899345, 4368374, N'04/03/2012', N'alex@gmail.com', N'Male', N'alex', N'alex123', 1, N'alex.jpg')
INSERT [dbo].[Manager] ([CiManager], [FirstName], [LastName], [Address], [CellPhone], [Phone], [DateInscription], [Email], [Gender], [UserName], [Password], [StateManager], [ImageManager]) VALUES (N'6588726', N'Gary', N'Antoriano', N'Quillacollo Barrio Coboce', 76999024, 4362734, N'04/03/2012', N'garyantoriano@hotmail.com', N'Male', N'gary', N'gary123', 1, N'gary.jpg')
INSERT [dbo].[Manager] ([CiManager], [FirstName], [LastName], [Address], [CellPhone], [Phone], [DateInscription], [Email], [Gender], [UserName], [Password], [StateManager], [ImageManager]) VALUES (N'6600234', N'Victor Hugo', N'Peña', N'Avenida Base aerea', 72896745, 4495434, N'04/03/2012', N'victor@hotmail.com', N'Male', N'victor', N'victor123', 1, N'victor.jpg')
INSERT [dbo].[Manager] ([CiManager], [FirstName], [LastName], [Address], [CellPhone], [Phone], [DateInscription], [Email], [Gender], [UserName], [Password], [StateManager], [ImageManager]) VALUES (N'6600111', N'Marial Carol', N'Villa', N'Avenida America', 72429978, 4495111, N'04/03/2012', N'marial@hotmail.com', N'Male', N'marial', N'marial123', 1, N'marial.jpg')

INSERT [dbo].[ManagerRole] ([Manager_CiManager], [Role_IdRole]) VALUES (N'6600234', 1)
INSERT [dbo].[ManagerRole] ([Manager_CiManager], [Role_IdRole]) VALUES (N'4494524', 2)
INSERT [dbo].[ManagerRole] ([Manager_CiManager], [Role_IdRole]) VALUES (N'6367889', 2)
INSERT [dbo].[ManagerRole] ([Manager_CiManager], [Role_IdRole]) VALUES (N'6588726', 1)
INSERT [dbo].[ManagerRole] ([Manager_CiManager], [Role_IdRole]) VALUES (N'6600111', 3)


INSERT [dbo].[Patient] ([CiPatient], [FirstName], [LastName], [Gender], [BirthDay], [CellPhone],[Phone],  [Address], [Email], [StatePatient], [ImagePatient]) VALUES (N'5736789', N'Samuel', N'Mendosa', N'Male', N'10/12/1988', 73883820, 47364756, N'Cochabamba, av America', N'samuel@hotmail.com',1, N'samuel.jpg')
INSERT [dbo].[Patient] ([CiPatient], [FirstName], [LastName], [Gender], [BirthDay], [CellPhone],[Phone],  [Address], [Email], [StatePatient], [ImagePatient]) VALUES (N'5733999', N'Manuel', N'Rivas', N'Male', N'11/11/1984', 73883823, 47364785, N'Cochabamba, av Siles', N'Manuel@hotmail.com',1, N'manuel.jpg')
INSERT [dbo].[Patient] ([CiPatient], [FirstName], [LastName], [Gender], [BirthDay], [CellPhone],[Phone],  [Address], [Email], [StatePatient], [ImagePatient]) VALUES (N'5733444', N'Cristina', N'Bastos', N'Female', N'10/12/1988', 73883111, 47364111, N'Cochabamba, av America', N'cristina@hotmail.com',1, N'cristina.jpg')

INSERT [dbo].[NoteBook] ([NameNoteBook], [StateNoteBook]) VALUES (N'NoteBook 1', N'Activo')
INSERT [dbo].[NoteBook] ([NameNoteBook], [StateNoteBook]) VALUES (N'NoteBook 2', N'Activo')

INSERT [dbo].[Diagnostic] ([DateDiagnostic], [NameDiagnostic], [NameTreatment], [AdverseCircumstances], [Observation], [StateDiagnostic],[Patient_CiPatient], [Manager_CiDoctor]) VALUES (N'04/04/2012', N'Destructive Dental Pathology', N'Extractions', N'Poor Hygiene', N'Deformacion',N'Activo',5736789,4494524)

INSERT [dbo].[Product] ([IdProduct],[GroupProduct], [NameProduct], [StockCurrent]) VALUES (2023,N'Anestesia', N'Jeringa', 10)

INSERT [dbo].[Buy] ([DateBuy],[Supplier], [StockOrder], [UnitPrice], [TotalPrice], [ImageBuy],[Description],[Product_IdProduct],[Manager_CiManager]) VALUES ( N'08/05/2012',N'Depositos dentales', 20, 2, 40, N'Jeringa.jpg',N'Jeringa para ñinos',2023, N'6600234')

INSERT [dbo].[Payment] ([CiPatient], [CiUser], [DatePayment], [Amount], [Balance], [TotalPayment], [Description]) VALUES (N'5736789', N'4494524', N'09/05/2012', 100, 250, 350, NULL)


----insert into --
--INSERT [dbo].[Agenda] ([DateAgenda], [TimeAgenda], [CiDoctor], [CiPatient], [StateAgenda],[NDiagnostic]) VALUES (N'5/10/2012', N'17:00', N'6600234', N'6736789', N'Confirmado', 1)
--INSERT [dbo].[Agenda] ([DateAgenda], [TimeAgenda], [CiDoctor], [CiPatient], [StateAgenda],[NDiagnostic]) VALUES (N'5/12/2012', N'09:00', N'6600234', N'6733999', N'Confirmado', 1)


INSERT [dbo].[Function] ([NameFunction], [Description]) VALUES (N'AgendaDoctor', NULL)
INSERT [dbo].[Function] ([NameFunction], [Description]) VALUES (N'AssignRole', NULL)
INSERT [dbo].[Function] ([NameFunction], [Description]) VALUES (N'ChangePasswordUser', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'CreateAnEditRole', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'Main', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'ModifyPatient', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'ModifyUser', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'NewAppointment', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'NewCash', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'NewDiagnosticPatient', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'NewPatient', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'NewProduct', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'RegisterUser', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'SearchPatient', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'SearchProduct', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'SearchUser', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'TodayAppointment', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'TreatmentPatient', NULL)
--INSERT [dbo].[Functions] ([NameFunction], [Description]) VALUES (N'ViewImageXRay', NULL)


INSERT [dbo].[RoleFunction] ([Role_IdRole], [Function_IdFunction]) VALUES (1, 1)
INSERT [dbo].[RoleFunction] ([Role_IdRole], [Function_IdFunction]) VALUES (1, 2)
INSERT [dbo].[RoleFunction] ([Role_IdRole], [Function_IdFunction]) VALUES (1, 3)
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'CreateAnEditRole')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'Main')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'ModifyPatient')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'ModifyUser')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'NewAppointment')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'NewCash')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'NewDiagnosticPatient')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'NewPatient')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'NewProduct')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'RegisterUser')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'SearchPatient')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'SearchProduct')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'SearchUser')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'TodayAppointment')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'TreatmentPatient')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Administrador', N'ViewImageXRay')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Doctor', N'AgendaDoctor')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Doctor', N'ChangePasswordUser')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Doctor', N'Main')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Doctor', N'NewAppointment')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Doctor', N'NewDiagnosticPatient')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Doctor', N'SearchPatient')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Doctor', N'SearchProduct')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Doctor', N'TodayAppointment')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Doctor', N'TreatmentPatient')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Doctor', N'ViewImageXRay')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Secretaria', N'AgendaDoctor')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Secretaria', N'Main')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Secretaria', N'ModifyPatient')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Secretaria', N'NewAppointment')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Secretaria', N'NewCash')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Secretaria', N'NewPatient')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Secretaria', N'NewProduct')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Secretaria', N'SearchPatient')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Secretaria', N'SearchProduct')
--INSERT [dbo].[FunctionRole] ([NameRole], [NameFunction]) VALUES (N'Secretaria', N'SearchUser')



